package insertData;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class CustomInsertRandomData2022 {
    private static Integer currentId = 0;
    private static HashMap<String,Integer> idmap = new HashMap<>();
    private static Connection connection = null;

    static {
        try {
            connection = DatabaseConnection.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) {
        startInsert();
    }

    public static void startInsert() {
        try {

            // Replace these values with the appropriate start and end years
            int startYear = 2022;
            int endYear = 2022;

            Random random = new Random();
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Calendar calendar = Calendar.getInstance();
            calendar.set(startYear, Calendar.JANUARY, 1);

            for (int year = startYear; year <= endYear; year++) {
                List<String> wbs = new ArrayList<>();

                int totalRecords = 15_000_000;
                int recordsPerMonth = totalRecords / 12;

                for (int month = 1; month <= 12; month++) {
                    List<String> modedatacreatedates = new ArrayList<>();
                    String placeHolder = month>9?"":"0";
                    String tableSuffix = year + placeHolder + month;
                    String tableName = "uf_t0_" + tableSuffix;
                    calendar.set(year, month-1, random.nextInt(28) + 1);
                    currentId =  getNextId(4,Integer.parseInt(tableSuffix));
                    int monthRecords = recordsPerMonth + random.nextInt(recordsPerMonth); // Vary the records
                    long time = System.currentTimeMillis();
                    for (int i = 0; i < monthRecords; i++) {
                        Date modedatacreatedate = calendar.getTime();
                        String wb = generateRandomString(random, 5);
                        modedatacreatedates.add(dateFormat.format(modedatacreatedate));
                        wbs.add(wb);

                        if (modedatacreatedates.size() == 1000) {
                            insertBatchDataIntoTable(connection, tableName, modedatacreatedates, wbs, getFormbizId(4,Integer.parseInt(tableSuffix)));
                            modedatacreatedates.clear();
                            wbs.clear();
                            long end = System.currentTimeMillis();

                            System.out.println("tableSuffix" + tableSuffix +"\tcurrid:"+currentId+"\t time:"+(end-time));
                            time = end;
                        }
                    }
                    if (!modedatacreatedates.isEmpty()) {
                        insertBatchDataIntoTable(connection, tableName, modedatacreatedates, wbs, getFormbizId(4,Integer.parseInt(tableSuffix)));
                        modedatacreatedates.clear();
                        wbs.clear();
                        System.out.println("tableSuffix" + tableSuffix +"\tcurrid:"+currentId);
                    }
                }


            }

            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static String generateRandomDate(int year, Random random, DateFormat dateFormat) {
        int month = random.nextInt(12) + 1;
        int day = random.nextInt(28) + 1; // Assume all months have max 28 days for simplicity
        return year + "-" + String.format("%02d", month) + "-" + String.format("%02d", day);
    }

    public static String generateRandomString(Random random, int length) {
        String characters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < length; i++) {
            int index = random.nextInt(characters.length());
            sb.append(characters.charAt(index));
        }
        return sb.toString();
    }

    public static void insertBatchDataIntoTable(Connection connection, String tableName, List<String> modedatacreatedates, List<String> wbs, String formBizidSuffix) throws SQLException {
        int id = currentId;
        String insertQuery = "INSERT INTO " + tableName + " (rq,modedatacreatedate, wb,formmodeid,modedatacreater,modedatacreatertype,form_biz_id,id) VALUES (?,?,?, ?, ?,?,?,?)";
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertQuery)) {
            for (int i = 0; i < modedatacreatedates.size(); i++) {
                preparedStatement.setString(1, modedatacreatedates.get(i));
                preparedStatement.setString(2, modedatacreatedates.get(i));
                preparedStatement.setString(3, wbs.get(i));
                preparedStatement.setInt(4, 4);
                preparedStatement.setInt(5, 1);
                preparedStatement.setInt(6, 0);
                preparedStatement.setString(7, id+formBizidSuffix);
                preparedStatement.setInt(8,id++);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
        }
        currentId = id;
    }

    public static String getFormbizId(int modeid, int year) {
        String formBizSuffix = "";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("select * from mode_sharding_bill where modeid = " + modeid + " and year = " + year);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                formBizSuffix = resultSet.getString("id");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        int i = 6 - formBizSuffix.length();
        StringBuilder sb = new StringBuilder();
        for (int i1 = 0; i1 < i; i1++) {
            sb.append("0");
        }
        sb.append(formBizSuffix);
        return sb.toString();
    }

    public static Integer getNextId(int modeid, int year) {
        int nextId = -1;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("select max(id) as id from uf_t0_" + year);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                nextId = resultSet.getInt("id")+1;

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return nextId;
    }


}
